﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FU.SE0208.G1.Entities;

namespace FU.SE0208.G1.Controller
{
    public class ProductHandler
    {
        // Store procedure to get Product ID list
        private const string STR_PRO_ID_LIST = "Handler_ProId_List";

        // Store procedure to get Product by Product ID
        private const string STR_PRODUCT_BY_ID = "Handler_Product_By_Id";

        private const string STR_SERIAL_LIST_BY_PRO_ID = "Handler_Serial_List_By_ProId";

        public bool addnew(Product obj)
        {
            SqlConnection conn = DbHandler.getConn();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("AddProduct", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int)).Value = obj.CategoryID1;
                command.Parameters.Add(new SqlParameter("@ManID", SqlDbType.Int)).Value = obj.ManID1;
                command.Parameters.Add(new SqlParameter("@MainID", SqlDbType.Int)).Value = obj.MainID1;
                command.Parameters.Add(new SqlParameter("@DisID", SqlDbType.Int)).Value = obj.DisID1;
                int count = command.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                    return true;
            }
            catch (Exception e) { return false; }
            finally { conn.Close(); conn.Dispose(); }
        }

        public bool del(Product obj)
        {
            SqlConnection conn = DbHandler.getConn();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("DeleteProduct", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@ProID", SqlDbType.Int)).Value = obj.ProID1;
                int count = command.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                    return true;
            }
            catch (Exception e) { return false; }
            finally { conn.Close(); conn.Dispose(); }
        }

        public bool edit(Product obj, Product newObj)
        {
            SqlConnection conn = DbHandler.getConn();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("EditProduct", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@ProID", SqlDbType.Int)).Value = obj.ProID1;
                command.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int)).Value = newObj.CategoryID1;
                command.Parameters.Add(new SqlParameter("@ManID", SqlDbType.Int)).Value = newObj.ManID1;
                command.Parameters.Add(new SqlParameter("@MainID", SqlDbType.Int)).Value = newObj.MainID1;
                command.Parameters.Add(new SqlParameter("@DisID", SqlDbType.Int)).Value = newObj.DisID1;
                
                int count = command.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                    return true;
            }
            catch (Exception e) { return false; }
            finally { conn.Close(); conn.Dispose(); }
        }

        public DataSet getAll(string table)
        {
            DataSet ds = new DataSet("G1");
            DbHandler.getQuery(ds, "Select * from [" + table + "]", table);
            return ds;
        }

        public bool getProIdList(DataSet ds, string sTable)
        {
            if (DbHandler.getQuery(ds, STR_PRO_ID_LIST, sTable) == false)
            {
                return false;
            }

            return true;
        }

        public bool getProductById(DataSet ds, string sTable, string sProId)
        {
            string sSQL = "SELECT B.ProId as [Product ID]," +
                                 "B.Warranty as [Product quantity]," + 
		                         "B.Name," + 
	                             "B.Price," +
	                             "B.Detail," +
	                             "B.Unit," +
	                             "B.Tax," + 
	                             "B.Warranty," +
	                             "C.[Type] as [Discount type]," +
	                             "C.Value as [Discount value]," +
	                             "C.[Status] as [Discount Status] " +
	                      "FROM Product as B, Discount as C " +
                          "WHERE B.ProId = '" + sProId + "' and B.DisId = C.DisId";

            if (DbHandler.getQuery(ds, sSQL, sTable) == false)
            {
                return false;
            }

            return true;
        }

        public bool getSerialByProId(DataSet ds, string sTable, string sProId)
        {
            string sSQL = "SELECT A.SerId " +
	                      "FROM Serial as A " +
                          "WHERE A.ProId = '" + sProId + "' and SellStatus = 'N'";

            if (DbHandler.getQuery(ds, sSQL, sTable) == false)
            {
                return false;
            }

            return true;
        }
    }
}
